package org.jeecg.modules.demo.gmslopedata1.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;

import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.modules.demo.gmslopedata.controller.GmSlopeDataController;
import org.jeecg.modules.demo.gmslopedata.controller.SockTypeController;
import org.jeecg.modules.demo.gmslopedata.entity.GmSlopeData;
import org.jeecg.modules.demo.gmslopedata.service.IGmSlopeDataService;
import org.jeecg.modules.demo.gmslopedata1.entity.GmSlopeData1;
import org.jeecg.modules.demo.gmslopedata1.entity.ImageData;
import org.jeecg.modules.demo.gmslopedata1.mapper.GmSlopeData1Mapper;
import org.jeecg.modules.demo.gmslopedata1.service.IGmSlopeData1Service;
import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import javax.imageio.ImageIO;
import javax.imageio.ImageReader;
import javax.imageio.stream.ImageInputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.*;
import java.util.concurrent.*;

/**
 * @Description: 边坡数据
 * @Author: jeecg-boot
 * @Date:   2025-06-24
 * @Version: V1.0
 */
@Service
@Slf4j
public class GmSlopeData1ServiceImpl extends ServiceImpl<GmSlopeData1Mapper, GmSlopeData1> implements IGmSlopeData1Service {
    // 图片配置（可根据需求调整）
    private static final int IMG_MAX_WIDTH_PX = 120;  // 图片最大宽度（像素）
    private static final int IMG_MAX_HEIGHT_PX = 120; // 图片最大高度（像素）
    private static final int IMG_MARGIN_PX = 10;      // 图片与单元格边框的间隙（像素）
    private static final int IMG_SPACING_PX = 15;     // 多张图片之间的间距（像素）

    @Autowired
    private IGmSlopeDataService gmSlopeDataService;

    @Autowired
    private GmSlopeDataController gmSlopeDataController;
    // 注入自定义线程池
    @Autowired
    @Qualifier("gmDataThreadPool")
    private ThreadPoolTaskExecutor taskExecutor;


    /**
     * 通过excel导入数据
     *
     * @param request
     * @param response
     * @return
     */
    public Result<?> importExcelCustom(HttpServletRequest request, HttpServletResponse response, Class<GmSlopeData1> clazz) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            // 获取上传文件对象
            MultipartFile file = entity.getValue();
            ImportParams params = new ImportParams();
            params.setTitleRows(2);
            params.setHeadRows(1);
            params.setNeedSave(true);
            try {
                List<GmSlopeData1> list = ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);
                //update-begin-author:taoyan date:20190528 for:批量插入数据
                for (int i = 0; i < list.size(); i++) {
                    list.get(i).setFlag("1");
                }
                long start = System.currentTimeMillis();
                this.saveBatch(list);
                //400条 saveBatch消耗时间1592毫秒  循环插入消耗时间1947毫秒
                //1200条  saveBatch消耗时间3687毫秒 循环插入消耗时间5212毫秒
                log.info("消耗时间" + (System.currentTimeMillis() - start) + "毫秒");
                //update-end-author:taoyan date:20190528 for:批量插入数据
                return Result.ok("文件导入成功！数据行数：" + list.size());
            } catch (Exception e) {
                //update-begin-author:taoyan date:20211124 for: 导入数据重复增加提示
                String msg = e.getMessage();
                log.error(msg, e);
                if (msg != null && msg.indexOf("Duplicate entry") >= 0) {
                    return Result.error("文件导入失败:有重复数据！");
                } else {
                    return Result.error("文件导入失败:" + e.getMessage());
                }
                //update-end-author:taoyan date:20211124 for: 导入数据重复增加提示
            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.error("文件导入失败！");
    }

    @Override
    public void exportXls(List<GmSlopeData1> exportList) {

    }


//    @Override
//    public void generateExcelBytes(List<GmSlopeData1> exportList, String outputPath) {
//        try (Workbook workbook = new XSSFWorkbook()) {
//            Sheet sheet = workbook.createSheet("导出记录");
//
//            // 设置列宽（保持不变）
//            sheet.setColumnWidth(0, 20 * 256);
//            sheet.setColumnWidth(1, 80 * 256);
//            sheet.setColumnWidth(2, 15 * 256);
//            sheet.setColumnWidth(3, 20 * 256);
//            sheet.setColumnWidth(4, 15 * 256);
//            sheet.setColumnWidth(5, 35 * 256);
//
//            // 创建样式（保持不变）
//            CellStyle headerStyle = createHeaderStyle(workbook);
//            CellStyle dataStyle = createDataStyle(workbook);
//
//            // 表头行（保持不变）
//            Row headerRow = sheet.createRow(0);
//            headerRow.setHeightInPoints(25);
//            String[] headers = {"编号", "稳定性计算结果", "地理位置", "户主姓名", "治理方案", "治理图集"};
//            for (int i = 0; i < headers.length; i++) {
//                Cell cell = headerRow.createCell(i);
//                cell.setCellValue(headers[i]);
//                cell.setCellStyle(headerStyle);
//            }
//
//            // 填充数据行（保持不变）
//            int rowNum = 1;
//            for (GmSlopeData1 gmSlopeData1 : exportList) {
//                Row row = sheet.createRow(rowNum++);
//                setCellValue(row, 0, gmSlopeData1.getNumber(), dataStyle);
//                setCellValue(row, 1, gmSlopeData1.getResult(), dataStyle);
//                setCellValue(row, 2, gmSlopeData1.getPosition(), dataStyle);
//                setCellValue(row, 3, gmSlopeData1.getHouseholder(), dataStyle);
//                setCellValue(row, 4, gmSlopeData1.getMeasure(), dataStyle);
//
//                // 处理图片列（保持不变）
//                Cell imageCell = row.createCell(5);
//                imageCell.setCellStyle(dataStyle);
//                if (gmSlopeData1.getImage() != null && !gmSlopeData1.getImage().trim().isEmpty()) {
//                    insertImagesWithMargin(sheet, workbook, row, 5, gmSlopeData1.getImage());
//                }
//            }
//
//            // 将Excel写入字节数组（关键修改）
//            ByteArrayOutputStream baos = new ByteArrayOutputStream();
//            workbook.write(baos);
//            return baos.toByteArray(); // 返回字节数组
//            // 写入文件（保持不变）
//            try (FileOutputStream outputStream = new FileOutputStream(outputPath)) {
//                workbook.write(outputStream);
//                System.out.println("导出成功，文件保存至: " + outputPath);
//            }
//        } catch (Exception e) {
//            System.err.println("导出Excel失败: " + e.getMessage());
//            e.printStackTrace();
//        }
////        } catch (IOException e) {
////            throw new RuntimeException(e);
////        }
//    }

@Override
public byte[] generateExcelBytes(List<GmSlopeData1> exportList) {
    try (Workbook workbook = new XSSFWorkbook()) {
        Sheet sheet = workbook.createSheet("导出记录");

        // 设置列宽（包含治理图集列）
        int[] colWidths = {20, 20, 15, 15, 20, 35};  // 6列宽度设置
        for (int i = 0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, colWidths[i] * 256);
        }

        // 创建样式
        CellStyle headerStyle = createHeaderStyle(workbook);
        CellStyle dataStyle = createDataStyle(workbook);

        // 表头行 - 修复：包含治理图集列的表头
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(25);
        String[] headers = {"编号", "稳定性计算结果", "地理位置", "户主姓名", "治理方案", "治理图集"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }

        // 并行预下载所有图片
        Map<Integer, List<ImageData>> imageCache = new ConcurrentHashMap<>();
//        ExecutorService executor = Executors.newFixedThreadPool(10);

        List<Future<?>> futures = new ArrayList<>();
        for (int i = 0; i < exportList.size(); i++) {
            int rowIndex = i;
            GmSlopeData1 data = exportList.get(i);
            if (data.getImage() != null && !data.getImage().trim().isEmpty()) {
                futures.add(taskExecutor.submit(() ->
                        downloadImagesForRow(data.getImage(), rowIndex, imageCache)
                ));
            }
        }

        // 等待所有下载完成
        for (Future<?> future : futures) {
            try {
                future.get();
            } catch (InterruptedException | ExecutionException e) {
                System.err.println("图片下载失败: " + e.getMessage());
            }
        }
        taskExecutor.shutdown();

        // 填充数据行
        for (int i = 0; i < exportList.size(); i++) {
            GmSlopeData1 gmSlopeData1 = exportList.get(i);
            Row row = sheet.createRow(i + 1);

            setCellValue(row, 0, gmSlopeData1.getNumber(), dataStyle);
            setCellValue(row, 1, gmSlopeData1.getResult(), dataStyle);
            setCellValue(row, 2, gmSlopeData1.getPosition(), dataStyle);
            setCellValue(row, 3, gmSlopeData1.getObligee(), dataStyle);
            setCellValue(row, 4, gmSlopeData1.getMeasure(), dataStyle);

            // 治理图集列：始终创建单元格（即使没有图片）
            Cell imageCell = row.createCell(5);
            imageCell.setCellStyle(dataStyle);

            // 插入图片（如果有）
            List<ImageData> cachedImages = imageCache.get(i);
            if (cachedImages != null && !cachedImages.isEmpty()) {
                insertCachedImages(sheet, workbook, row, 5, cachedImages);
            }
        }
        // 将Excel写入字节数组（关键修改）
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        return baos.toByteArray(); // 返回字节数组
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

    @Override
    public List<GmSlopeData1> readExcel(MultipartFile excelFile) {
        List<GmSlopeData1> dataList = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(excelFile.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    continue; // 跳过表头行
                }
                GmSlopeData1 data = new GmSlopeData1();
                GmSlopeData gmSlopeData = new GmSlopeData();
                data.setPatchNumber(getCellValue(row.getCell(0)));
                data.setPosition(getCellValue(row.getCell(1)));
                data.setProvince(getCellValue(row.getCell(2)));
                data.setCity(getCellValue(row.getCell(3)));
                data.setCounty(getCellValue(row.getCell(4)));
                data.setTown(getCellValue(row.getCell(5)));
                data.setVillage(getCellValue(row.getCell(6)));
                data.setTeam(getCellValue(row.getCell(7)));
                data.setNumber(getCellValue(row.getCell(8)));
                // 在 readExcel 方法中修改以下代码
                double longitude = getDoubleCellValue(row.getCell(9)) != null ?
                        getDoubleCellValue(row.getCell(9)) : 0.0;
                double latitude = getDoubleCellValue(row.getCell(10)) != null ?
                        getDoubleCellValue(row.getCell(10)) : 0.0;
                data.setLongitude(longitude);
                data.setLatitude(latitude);
                data.setHouseholder(getCellValue(row.getCell(11)));
                data.setObligee(getCellValue(row.getCell(12)));
                data.setClassificationNumber(getCellValue(row.getCell(13)));
                data.setSlopeType(getCellValue(row.getCell(14)));
                data.setSoilLooseness(getCellValue(row.getCell(15)));
                data.setOverlyingSoilType(getCellValue(row.getCell(16)));
                data.setRockCharacteristics(getCellValue(row.getCell(17)));
                data.setFractureDevelopmentDegree(getCellValue(row.getCell(18)));
                data.setCutSlopeHeight(getCellValue(row.getCell(19)));
                data.setCutSlopeGradient(getCellValue(row.getCell(20)));
                data.setCutSlopeShape(getCellValue(row.getCell(21)));
                data.setNaturalSlopeGradient(getCellValue(row.getCell(22)));
                data.setRockType(getCellValue(row.getCell(23)));
                data.setVegetationCoverage(getCellValue(row.getCell(24)));
                data.setGovernanceMeasures(getCellValue(row.getCell(25)));
                data.setArtificialStability(getCellValue(row.getCell(26)));
                data.setSlopeWallDistance(getCellValue(row.getCell(27)));
                data.setCutSlopeWidth(getCellValue(row.getCell(28)));
                data.setCutSlopeTime(getCellValue(row.getCell(29)));
                data.setResident(getCellValue(row.getCell(30)));
                data.setThreatPopulation(getCellValue(row.getCell(31)));
                data.setThreatHouse(getCellValue(row.getCell(32)));
                //将GmSlopeData1转成GmSlopeData对象
                BeanUtils.copyProperties(data, gmSlopeData);
                System.out.println("gmSlopeData = " + gmSlopeData);
                gmSlopeDataController.getMeasure(gmSlopeData);
                dataList.add(data);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        return dataList;
    }

    private String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    private Double getDoubleCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        }

        try {
            String cellValue = getCellValue(cell);
            if (cellValue == null || cellValue.trim().isEmpty()) {
                return null;
            }
            return Double.parseDouble(cellValue.trim());
        } catch (NumberFormatException e) {
            return null;
        }
    }

    //--- 图片下载逻辑（并行执行）---
    private void downloadImagesForRow(String imageUrls, int rowIndex, Map<Integer, List<ImageData>> cache) {
        List<ImageData> imageDataList = new ArrayList<>();
        for (String urlStr : imageUrls.split(",")) {
            urlStr = urlStr.trim().replace("\\", "/");
            if (urlStr.isEmpty()) continue;

            try {
                URL url = new URL(urlStr);
                HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                conn.setConnectTimeout(3000);  // 缩短超时时间
                conn.setReadTimeout(5000);

                if (conn.getResponseCode() != HttpURLConnection.HTTP_OK) {
                    System.err.println("图片下载失败: " + urlStr);
                    continue;
                }

                try (InputStream is = conn.getInputStream()) {
                    byte[] bytes = IOUtils.toByteArray(is);
                    int type = getImageType(urlStr);

                    // 仅读取图片尺寸，不完整解码
                    ImageInputStream iis = ImageIO.createImageInputStream(new ByteArrayInputStream(bytes));
                    Iterator<ImageReader> readers = ImageIO.getImageReaders(iis);
                    if (!readers.hasNext()) continue;

                    ImageReader reader = readers.next();
                    reader.setInput(iis);
                    int width = reader.getWidth(0);
                    int height = reader.getHeight(0);
                    reader.dispose();

                    imageDataList.add(new ImageData(bytes, type, width, height));
                }
            } catch (Exception e) {
                System.err.println("下载图片失败: " + urlStr + " - " + e.getMessage());
            }
        }
        if (!imageDataList.isEmpty()) {
            cache.put(rowIndex, imageDataList);
        }
    }

    //--- 使用缓存的图片数据插入 ---
    private void insertCachedImages(Sheet sheet, Workbook workbook, Row row, int colIndex, List<ImageData> images) {
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        CreationHelper helper = workbook.getCreationHelper();

        int horizontalOffset = Units.pixelToEMU(IMG_MARGIN_PX);
        int maxHeightEMU = 0;
        int spacingEMU = Units.pixelToEMU(IMG_SPACING_PX);

        for (ImageData img : images) {
            // 计算缩放尺寸
            double scale = Math.min(
                    Math.min((double) IMG_MAX_WIDTH_PX / img.width, 1.0),
                    Math.min((double) IMG_MAX_HEIGHT_PX / img.height, 1.0)
            );
            int dispWidth = (int) (img.width * scale);
            int dispHeight = (int) (img.height * scale);

            int widthEMU = Units.pixelToEMU(dispWidth);
            int heightEMU = Units.pixelToEMU(dispHeight);
            maxHeightEMU = Math.max(maxHeightEMU, heightEMU);

            // 插入图片
            int picIdx = workbook.addPicture(img.data, img.type);
            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(colIndex);
            anchor.setCol2(colIndex);
            anchor.setRow1(row.getRowNum());
            anchor.setRow2(row.getRowNum());
            anchor.setDx1(horizontalOffset);
            anchor.setDx2(horizontalOffset + widthEMU);
            anchor.setDy1(Units.pixelToEMU(IMG_MARGIN_PX));
            anchor.setDy2(Units.pixelToEMU(IMG_MARGIN_PX) + heightEMU);

            drawing.createPicture(anchor, picIdx);
            horizontalOffset += widthEMU + spacingEMU;
        }

        // 调整列宽和行高
        int totalWidthEMU = horizontalOffset + Units.pixelToEMU(IMG_MARGIN_PX);
//        int columnWidth = Math.max(Units.emuToColumnWidth(totalWidthEMU), 35 * 256);
        int columnWidth = Math.max((int) Math.round(totalWidthEMU / (double) Units.EMU_PER_CHARACTER), 35 * 256);
        sheet.setColumnWidth(colIndex, columnWidth);


        if (maxHeightEMU > 0) {
            float rowHeight = (Units.pixelToEMU(IMG_MARGIN_PX * 2) + maxHeightEMU) / (float) Units.EMU_PER_POINT;
            row.setHeightInPoints(Math.max(rowHeight, 20));
        }
    }

//    /**
//     * 插入图片（带单元格间隙，避免紧挨着分界线）
//     */
//    private static void insertImagesWithMargin(Sheet sheet, Workbook workbook, Row row, int columnIndex, String imageUrls) {
//        String[] urls = imageUrls.split(",");
//        if (urls.length == 0) return;
//
//        Drawing<?> drawing = sheet.createDrawingPatriarch();
//        double totalRowHeight = 0; // 总行高（点）
//        final double marginPoints = IMG_MARGIN_PX * (72.0 / 96.0); // 间隙转换为点（1像素=72/96点）
//        final double spacingPoints = IMG_SPACING_PX * (72.0 / 96.0); // 图片间距转换为点
//
//        int currentColumn = columnIndex; // 当前插入列索引
//
//        for (String urlStr : urls) {
//            urlStr = urlStr.trim().replace("\\", "/");
//            if (urlStr.isEmpty()) continue;
//
//            try {
//                // 1. 下载图片
//                URL url = new URL(urlStr);
//                HttpURLConnection connection = (HttpURLConnection) url.openConnection();
//                connection.setRequestMethod("GET");
//                connection.setConnectTimeout(5000);
//                connection.setReadTimeout(10000);
//
//                int status = connection.getResponseCode();
//                if (status != HttpURLConnection.HTTP_OK) {
//                    System.err.println("图片下载失败: " + urlStr + "（HTTP状态码: " + status + "）");
//                    continue;
//                }
//
//                // 2. 读取图片原始尺寸
//                BufferedImage awtImg;
//                try (InputStream is = connection.getInputStream()) {
//                    awtImg = ImageIO.read(is);
//                }
//                if (awtImg == null) {
//                    System.err.println("无法读取图片格式: " + urlStr);
//                    continue;
//                }
//
//                // 3. 计算图片缩放比例（不放大，只缩小）
//                double widthRatio = (double) IMG_MAX_WIDTH_PX / awtImg.getWidth();
//                double heightRatio = (double) IMG_MAX_HEIGHT_PX / awtImg.getHeight();
//                double scale = Math.min(Math.min(widthRatio, heightRatio), 1.0);
//                int displayWidth = (int) (awtImg.getWidth() * scale);  // 缩放后宽度（像素）
//                int displayHeight = (int) (awtImg.getHeight() * scale); // 缩放后高度（像素）
//
//                // 4. 转换尺寸为Excel单位（EMU）
//                int widthEMU = Units.pixelToEMU(displayWidth);
//                int heightEMU = Units.pixelToEMU(displayHeight);
//                double imgHeightPoints = displayHeight * (72.0 / 96.0); // 图片高度（点）
//
//                // 5. 累加总行高（含间隙和间距）
//                if (totalRowHeight == 0) {
//                    totalRowHeight = marginPoints * 2 + imgHeightPoints; // 上下各留间隙
//                } else {
//                    totalRowHeight += spacingPoints + imgHeightPoints; // 增加间距+图片高度
//                }
//
//                // 6. 设置行高（适配图片+间隙）
//                row.setHeightInPoints((float) Math.max(totalRowHeight, row.getHeightInPoints()));
//
//                // 7. 插入图片（带间隙，避免紧挨着边框）
//                try (InputStream is = url.openStream()) {
//                    byte[] bytes = IOUtils.toByteArray(is);
//                    int imageType = getImageType(urlStr);
//                    int pictureIdx = workbook.addPicture(bytes, imageType);
//
//                    // 创建锚点：设置图片位置（左、上留间隙）
//                    CreationHelper helper = workbook.getCreationHelper();
//                    ClientAnchor anchor = helper.createClientAnchor();
//                    anchor.setCol1(currentColumn);          // 起始列（当前列）
//                    anchor.setCol2(currentColumn + 1);      // 结束列（下一列）
//                    anchor.setRow1(row.getRowNum());        // 起始行（当前行）
//                    anchor.setRow2(row.getRowNum() + 1);    // 结束行（下一行）
//
//                    // 关键：设置左、上间隙（10像素）
//                    int marginEMU = Units.pixelToEMU(IMG_MARGIN_PX);
//                    anchor.setDx1(marginEMU);               // 左边间隙（10像素）
//                    anchor.setDy1(marginEMU);               // 上边间隙
//                    anchor.setDx2(widthEMU - marginEMU);   // 图片宽度（减去左边距）
//                    anchor.setDy2(heightEMU - marginEMU);  // 图片高度（减去上边距）
//
//                    // 插入图片
//                    drawing.createPicture(anchor, pictureIdx);
//                }
//
//            } catch (Exception e) {
//                System.err.println("插入图片失败 [" + urlStr + "]: " + e.getMessage());
//            }
//
//            currentColumn++; // 切换到下一列
//        }
//
//        // 确保行高不小于最小值（避免间隙导致行高过小）
//        if (totalRowHeight < 20) {
//            row.setHeightInPoints(20);
//        }
//    }
    /**
     * 插入图片（同一列内左右排列，带单元格间隙）
     */
    private static void insertImagesWithMargin(Sheet sheet, Workbook workbook, Row row, int columnIndex, String imageUrls) {
        String[] urls = imageUrls.split(",");
        if (urls.length == 0) return;

        Drawing<?> drawing = sheet.createDrawingPatriarch();
        CreationHelper helper = workbook.getCreationHelper();

        // 1. 初始化水平偏移（从单元格左边间隙开始）
        int horizontalOffsetEMU = Units.pixelToEMU(IMG_MARGIN_PX); // 10像素→EMU（1像素=9525 EMU）
        // 2. 初始化最大图片高度（用于调整行高）
        int maxHeightEMU = 0;
        // 3. 图片间距（转换为EMU）
        int spacingEMU = Units.pixelToEMU(IMG_SPACING_PX); // 15像素→EMU

        for (String urlStr : urls) {
            urlStr = urlStr.trim().replace("\\", "/");
            if (urlStr.isEmpty()) continue;

            try {
                // 步骤1：下载图片并读取原始尺寸
                URL url = new URL(urlStr);
                HttpURLConnection connection = (HttpURLConnection) url.openConnection();
                connection.setRequestMethod("GET");
                connection.setConnectTimeout(5000);
                connection.setReadTimeout(10000);

                int status = connection.getResponseCode();
                if (status != HttpURLConnection.HTTP_OK) {
                    System.err.println("图片下载失败: " + urlStr + "（HTTP状态码: " + status + "）");
                    continue;
                }

                BufferedImage awtImg;
                try (InputStream is = connection.getInputStream()) {
                    awtImg = ImageIO.read(is);
                }
                if (awtImg == null) {
                    System.err.println("无法读取图片格式: " + urlStr);
                    continue;
                }

                // 步骤2：计算图片缩放比例（不放大，只缩小）
                double widthRatio = (double) IMG_MAX_WIDTH_PX / awtImg.getWidth();
                double heightRatio = (double) IMG_MAX_HEIGHT_PX / awtImg.getHeight();
                double scale = Math.min(Math.min(widthRatio, heightRatio), 1.0); // 避免放大图片
                int displayWidth = (int) (awtImg.getWidth() * scale);  // 缩放后宽度（像素）
                int displayHeight = (int) (awtImg.getHeight() * scale); // 缩放后高度（像素）

                // 步骤3：转换尺寸为EMU（Excel锚点单位）
                int widthEMU = Units.pixelToEMU(displayWidth);
                int heightEMU = Units.pixelToEMU(displayHeight);

                // 步骤4：更新最大图片高度（用于调整行高）
                if (heightEMU > maxHeightEMU) {
                    maxHeightEMU = heightEMU;
                }

                // 步骤5：插入图片（同一列内左右排列）
                try (InputStream is = url.openStream()) {
                    byte[] bytes = IOUtils.toByteArray(is);
                    int imageType = getImageType(urlStr);
                    int pictureIdx = workbook.addPicture(bytes, imageType);

                    // 创建锚点：同一行、同一列，水平偏移
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(columnIndex);          // 起始列（固定为"治理图集"列）
                    anchor.setCol2(columnIndex);          // 结束列（同一列，不跨列）
                    anchor.setRow1(row.getRowNum());      // 起始行（当前数据行）
                    anchor.setRow2(row.getRowNum());      // 结束行（同一行，不跨行）

                    // 设置水平位置：左边间隙+当前偏移量
                    anchor.setDx1(horizontalOffsetEMU);               // 图片左侧偏移（EMU）
                    anchor.setDx2(horizontalOffsetEMU + widthEMU);    // 图片右侧偏移（EMU）
                    // 设置垂直位置：上边间隙
                    anchor.setDy1(Units.pixelToEMU(IMG_MARGIN_PX));   // 图片顶部偏移（EMU）
                    anchor.setDy2(Units.pixelToEMU(IMG_MARGIN_PX) + heightEMU); // 图片底部偏移（EMU）

                    // 插入图片
                    drawing.createPicture(anchor, pictureIdx);
                }

                // 步骤6：更新水平偏移（下一张图片的起始位置 = 当前结束位置 + 间距）
                horizontalOffsetEMU += widthEMU + spacingEMU;

            } catch (Exception e) {
                System.err.println("插入图片失败 [" + urlStr + "]: " + e.getMessage());
            }
        }

        // 步骤7：调整列宽（容纳所有图片的总宽度+右边间隙）
        int totalWidthEMU = horizontalOffsetEMU + Units.pixelToEMU(IMG_MARGIN_PX); // 总宽度=最后一张图片结束位置+右边间隙
//        int columnWidth = Units.emuToColumnWidth(totalWidthEMU); // 将EMU转换为列宽单位（1列宽=256）
        int columnWidth = (int) (totalWidthEMU / (Units.EMU_PER_POINT * 7)); // 每个字符约等于 7 点宽度
        columnWidth = Math.max(columnWidth, 35 * 256); // 确保列宽不小于初始值（35列宽）
        sheet.setColumnWidth(columnIndex, columnWidth);

        // 步骤8：调整行高（最大图片高度+上下间隙）
        if (maxHeightEMU > 0) {
            int totalHeightEMU = Units.pixelToEMU(IMG_MARGIN_PX * 2) + maxHeightEMU; // 总高度=上下间隙+最大图片高度
            float rowHeightPoints = totalHeightEMU / (float) Units.EMU_PER_POINT; // 转换为点（行高单位）
            rowHeightPoints = Math.max(rowHeightPoints, 20); // 确保行高不小于最小值（20点）
            row.setHeightInPoints(rowHeightPoints);
        }
    }

    /**
     * 获取图片类型（根据URL后缀）
     */
    private static int getImageType(String url) {
        String ext = url.toLowerCase().substring(url.lastIndexOf(".") + 1);
        switch (ext) {
            case "png": return Workbook.PICTURE_TYPE_PNG;
            case "jpg": case "jpeg": return Workbook.PICTURE_TYPE_JPEG;
            case "bmp": return Workbook.PICTURE_TYPE_DIB;
            default: return Workbook.PICTURE_TYPE_JPEG; // 默认JPEG
        }
    }

    // ------------------- 以下方法保持不变（除合并单元格相关） -------------------
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //设置颜色为白色
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//        style.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        font.setFontName("宋体");
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        return style;
    }

    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setWrapText(true);
        style.setVerticalAlignment(VerticalAlignment.TOP);
        return style;
    }

    private static void setCellValue(Row row, int columnIndex, String value, CellStyle style) {
        if (row == null) return;
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
        // 文本内容过长时自动调整行高（保持不变）
        if (columnIndex == 1 && value != null && value.length() > 50) {
            row.setHeightInPoints(calculateRowHeight(value));
        }
    }

    private static float calculateRowHeight(String text) {
        int lineCount = (int) Math.ceil(text.length() / 80.0);
        return Math.max(20, 15 * lineCount);
    }

    // 【移除合并单元格功能】：删除mergeSameCells和centerMergedCells方法（或注释）
    // private static void mergeSameCells(Sheet sheet, int columnIndex) { ... }
    // private static void centerMergedCells(Sheet sheet, int firstRow, int lastRow, int column) { ... }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        return cell.getStringCellValue();
    }
}
